﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DevComponents.DotNetBar.Controls;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace qlhs
{
    public partial class Form1 : DevComponents.DotNetBar.Office2007RibbonForm
    {
        public DataClassDataContext db = new DataClassDataContext();
        public Boolean ThemHaySua = false;// Sua, danh cho nam hoc
        public Boolean ThemHaySuaTruong = false;// sua
        public Form1()
        {
            InitializeComponent();
        }
        
        private void Form1_Load(object sender, EventArgs e)
        {
            #region Lúc load form
            // TODO: This line of code loads data into the 'qlhsDataSet.DM_Truong' table. You can move, or remove it, as needed.
            this.dM_TruongTableAdapter.Fill(this.qlhsDataSet.DM_Truong);
            // TODO: This line of code loads data into the 'qlhsDataSet.DM_NamHoc' table. You can move, or remove it, as needed.
            this.dM_NamHocTableAdapter.Fill(this.qlhsDataSet.DM_NamHoc);
            //
            var p = (from ct in db.ThamSoHeThongs
                     join t in db.DM_Truongs
                     on ct.FK_TruongID equals t.TruongID
                     select t).Single();
            lblTruongDuocChon.Text = p.TenTruong;

            var p2 = (from cn in db.ThamSoHeThongs
                      join n in db.DM_NamHocs
                      on cn.NamHocID equals n.NamHocID
                      select n).Single();
            lblNamDuocChon.Text = p2.TenNamHoc;
            #endregion
        }        
        //
        public string LayIDDataGridView(DataGridViewX dt)
        {
            try
            {
                return dt.CurrentRow.Cells[0].Value.ToString();
            }
            catch (Exception ex) { Console.WriteLine(ex); }
            return null;
        }
        #region Thiết lập tham số hệ thống
        //THIẾT LẬP THAM SỐ HỆ THỐNG---------------------------------------------
        // NĂM
        #region Năm học
        //
        public void CapNhatNamHoc()
        {
            dtNamHoc.DataSource = null;
            var p = from nh in db.DM_NamHocs
                    select nh;
            dtNamHoc.DataSource = p;
        }
        //
        private void ChonNamHoc_Click(object sender, EventArgs e)
        {
            try
            {
                string NamHocID = dtNamHoc.CurrentRow.Cells[0].Value.ToString();
                var p = db.DM_NamHocs.Single(o => o.NamHocID.Equals(NamHocID));
                lblNamDuocChon.Text = p.TenNamHoc;

                ThamSoHeThong ts = db.ThamSoHeThongs.Single(o => o.id == 1);
                ts.NamHocID = NamHocID;

                Console.WriteLine(ts.NamHocID);
                db.SubmitChanges();
            }
            catch (Exception ex) { Console.WriteLine(ex); }
        }

        private void XoaNamHoc_Click(object sender, EventArgs e)
        {
            string NamHocID = LayIDDataGridView(dtNamHoc);
            if (NamHocID != null)
            {
                var p = db.DM_NamHocs.Single(o => o.NamHocID.Equals(NamHocID));
                db.DM_NamHocs.DeleteOnSubmit(p);
                db.SubmitChanges();

                CapNhatNamHoc();
            }
            else
            {
                MessageBox.Show("Chưa chọn!");
            }
        }

        private void SuaNamHoc_Click(object sender, EventArgs e)
        {
            SuaNamHoc.Enabled = false;
            LuuNamHoc.Enabled = true;
            tbxTenNamHoc.Enabled = true;
            tbxNamHocGhiChu.Enabled = true;
            ThemHaySua = false;
        }

        private void dtNamHoc_SelectionChanged(object sender, EventArgs e)
        {
            try
            {
                tbxNamHocID.Text = dtNamHoc.CurrentRow.Cells[0].Value.ToString();
                tbxTenNamHoc.Text = dtNamHoc.CurrentRow.Cells[1].Value.ToString();
                tbxNamHocGhiChu.Text = dtNamHoc.CurrentRow.Cells[2].Value.ToString();
            }
            catch (Exception ex) { Console.WriteLine(ex); }
        }

        private void LuuNamHoc_Click(object sender, EventArgs e)
        {
            if (ThemHaySua == false) //sua
            {
                string NamHocID = LayIDDataGridView(dtNamHoc);
                if (NamHocID != null)
                {
                    var p = db.DM_NamHocs.Single(o => o.NamHocID.Equals(NamHocID));
                    p.TenNamHoc = tbxTenNamHoc.Text;
                    p.GhiChu = tbxNamHocGhiChu.Text;

                    db.SubmitChanges();

                    CapNhatNamHoc();

                    LuuNamHoc.Enabled = false;
                    SuaNamHoc.Enabled = true;
                    tbxTenNamHoc.Enabled = false;
                    tbxNamHocGhiChu.Enabled = false;
                }
                else
                {
                    MessageBox.Show("Chưa chọn!");
                }
            }
            else // them
            {
                var check = from c in db.DM_NamHocs
                            where c.NamHocID.Equals(tbxNamHocID.Text)
                            select c;
                if (check.Count() > 0) { MessageBox.Show("Đã tồn tại!"); return; }

                DM_NamHoc nh = new DM_NamHoc();
                nh.NamHocID = tbxNamHocID.Text;
                nh.TenNamHoc = tbxTenNamHoc.Text;
                nh.GhiChu = tbxNamHocGhiChu.Text;

                db.DM_NamHocs.InsertOnSubmit(nh);

                db.SubmitChanges();

                CapNhatNamHoc();

                LuuNamHoc.Enabled = false;
                ThemNamHoc.Enabled = true;
                tbxNamHocID.Enabled = false;
                tbxTenNamHoc.Enabled = false;
                tbxNamHocGhiChu.Enabled = false;
            }

        }

        private void ThemNamHoc_Click(object sender, EventArgs e)
        {
            ThemNamHoc.Enabled = false;
            LuuNamHoc.Enabled = true;
            tbxNamHocID.Enabled = true;
            tbxTenNamHoc.Enabled = true;
            tbxNamHocGhiChu.Enabled = true;
            ThemHaySua = true;
        }
        #endregion

        // TRƯỜNG
        #region Trường
        //
        public void CapNhatTruong()
        {
            dtTruong.DataSource = null;
            var p = from nh in db.DM_Truongs
                    select nh;
            dtTruong.DataSource = p;
        }
        //

        private void ChonTruong_Click(object sender, EventArgs e)
        {
            try
            {
                string TruongID = dtTruong.CurrentRow.Cells[0].Value.ToString();
                var p = db.DM_Truongs.Single(o => o.TruongID.Equals(TruongID));
                lblTruongDuocChon.Text = p.TenTruong;

                ThamSoHeThong ts = db.ThamSoHeThongs.Single(o => o.id == 1);
                ts.FK_TruongID = TruongID;

                Console.WriteLine(ts.FK_TruongID);
                db.SubmitChanges();
            }
            catch (Exception ex) { Console.WriteLine(ex); }
        }

        private void ThemTruong_Click(object sender, EventArgs e)
        {
            ThemTruong.Enabled = false;
            LuuTruong.Enabled = true;

            tbxTruongID.Enabled = true;
            tbxTruongFax.Enabled = true;
            tbxTruongEmail.Enabled = true;
            tbxTruongDienThoai.Enabled = true;
            tbxTruongDiaChi.Enabled = true;
            tbxTenTruong.Enabled = true;

            ThemHaySuaTruong = true;
        }

        private void SuaTruong_Click(object sender, EventArgs e)
        {
            SuaTruong.Enabled = false;
            LuuTruong.Enabled = true;

            tbxTruongID.Enabled = false;
            tbxTruongFax.Enabled = true;
            tbxTruongEmail.Enabled = true;
            tbxTruongDienThoai.Enabled = true;
            tbxTruongDiaChi.Enabled = true;
            tbxTenTruong.Enabled = true;

            ThemHaySuaTruong = false;
        }

        private void XoaTruong_Click(object sender, EventArgs e)
        {
            string TruongID = LayIDDataGridView(dtTruong);
            if (TruongID != null)
            {
                var p = db.DM_Truongs.Single(o => o.TruongID.Equals(TruongID));

                db.DM_Truongs.DeleteOnSubmit(p);
                db.SubmitChanges();

                CapNhatTruong();
            }
            else
            {
                MessageBox.Show("Chưa chọn!");
            }
        }

        private void LuuTruong_Click(object sender, EventArgs e)
        {
            if (ThemHaySuaTruong == false) //sua
            {
                string TruongID = LayIDDataGridView(dtTruong);
                if (TruongID != null)
                {
                    var p = db.DM_Truongs.Single(o => o.TruongID.Equals(TruongID));

                    p.TenTruong = tbxTenTruong.Text;
                    p.DiaChi = tbxTruongDiaChi.Text;
                    p.Fax = tbxTruongFax.Text;
                    p.DienThoai = tbxTruongDienThoai.Text;
                    p.Email = tbxTruongEmail.Text;

                    db.SubmitChanges();

                    CapNhatTruong();

                    SuaTruong.Enabled = true;
                    LuuTruong.Enabled = false;

                    tbxTruongID.Enabled = false;
                    tbxTruongFax.Enabled = false;
                    tbxTruongEmail.Enabled = false;
                    tbxTruongDienThoai.Enabled = false;
                    tbxTruongDiaChi.Enabled = false;
                    tbxTenTruong.Enabled = false;
                }
                else
                {
                    MessageBox.Show("Chưa chọn!");
                }
            }
            else // them
            {
                var check = from c in db.DM_Truongs
                            where c.TruongID.Equals(tbxTruongID.Text)
                            select c;
                if (check.Count() > 0) { MessageBox.Show("Đã tồn tại!"); return; }

                DM_Truong p = new DM_Truong();

                p.TruongID = tbxTruongID.Text;
                p.TenTruong = tbxTenTruong.Text;
                p.DiaChi = tbxTruongDiaChi.Text;
                p.Fax = tbxTruongFax.Text;
                p.DienThoai = tbxTruongDienThoai.Text;
                p.Email = tbxTruongEmail.Text;

                db.DM_Truongs.InsertOnSubmit(p);
                db.SubmitChanges();

                CapNhatTruong();

                ThemTruong.Enabled = true;
                LuuTruong.Enabled = false;

                tbxTruongID.Enabled = false;
                tbxTruongFax.Enabled = false;
                tbxTruongEmail.Enabled = false;
                tbxTruongDienThoai.Enabled = false;
                tbxTruongDiaChi.Enabled = false;
                tbxTenTruong.Enabled = false;
            }
        }

        private void dtTruong_SelectionChanged(object sender, EventArgs e)
        {
            try
            {
                tbxTruongID.Text = dtTruong.CurrentRow.Cells[0].Value.ToString();
                tbxTenTruong.Text = dtTruong.CurrentRow.Cells[1].Value.ToString();
                tbxTruongDiaChi.Text = dtTruong.CurrentRow.Cells[2].Value.ToString();
                tbxTruongDienThoai.Text = dtTruong.CurrentRow.Cells[3].Value.ToString();
                tbxTruongFax.Text = dtTruong.CurrentRow.Cells[4].Value.ToString();
                tbxTruongEmail.Text = dtTruong.CurrentRow.Cells[5].Value.ToString();
            }
            catch (Exception ex) { Console.WriteLine(ex); }
        }
        #endregion
        // END THIẾT LẬP THAM SỐ HỆ THỐNG----------------------------------------
        #endregion

        private void ribbonTabItem2_Click(object sender, EventArgs e)
        {
            #region Chuyển qua tab nhập học sinh, trước tiên ẩn đi các control
            dtHocSinh.DataSource = null;
            dtHocSinhNhapTuFile.DataSource = null;

            dtHocSinh.Visible = false;
            dtHocSinhNhapTuFile.Visible = false;

            lblDuongDan.Visible = false;
            tbxDuongDan.Visible = false;
            btnChonFile.Visible = false;
            btnLoadDuLieu.Visible = false;
            btnLuuDuLieu.Visible = false;
            btnLuuNhapTay.Visible = false;
            #endregion

            var p = (from ts in db.ThamSoHeThongs
                     join nh in db.DM_NamHocs on ts.NamHocID equals nh.NamHocID
                     select nh).Single();
            tbxNhapHSNamHoc.Text = p.TenNamHoc;
        }

        SqlConnection con;
        SqlDataAdapter ap;
        DataTable tb;
        private void NhapHSTay_Click(object sender, EventArgs e)
        {
            #region Hiện các box liên quan đến nhập tay
            btnLuuNhapTay.Visible = true;
            lblDuongDan.Visible = false;
            tbxDuongDan.Visible = false;
            btnChonFile.Visible = false;
            btnLoadDuLieu.Visible = false;
            btnLuuDuLieu.Visible = false;
            dtHocSinh.Visible = true;
            dtHocSinh.DataSource = null;
            dtHocSinhNhapTuFile.Visible = false;
            #endregion
            dtHocSinh.Columns.Clear();
            #region Binding dữ liệu với datagrid dtHocSinh
            con = new SqlConnection(@"Data Source=LAM\SQLEXPRESS;Initial Catalog=qlhs;Integrated Security=True");
            con.Open();
            ap = new SqlDataAdapter("SELECT * FROM HS_HoSoHocSinh WHERE DaPhanLop = 0", con);
            tb = new DataTable();
            // Fill table
            ap.Fill(tb);
            // Tạo binding source
            BindingSource bs = new BindingSource();
            bs.DataSource = tb;

            //Quoc Gia Data Source
            string QueryQuocGia = "SELECT QuocGiaID,TenQuocGia FROM DM_QuocGia";
            SqlDataAdapter sqlDataAdapterQuocGia = new SqlDataAdapter(QueryQuocGia, con);
            DataTable dataTableQuocGia = new DataTable();
            sqlDataAdapterQuocGia.Fill(dataTableQuocGia);
            BindingSource bindingSourceQuocGia = new BindingSource();
            bindingSourceQuocGia.DataSource = dataTableQuocGia;
            //Dan toc Data Source
            string QueryDanToc = "SELECT DanTocID,TenDanToc FROM DM_DanToc";
            SqlDataAdapter sqlDataAdapterDanToc = new SqlDataAdapter(QueryDanToc, con);
            DataTable dataTableDanToc = new DataTable();
            sqlDataAdapterDanToc.Fill(dataTableDanToc);
            BindingSource bindingSourceDanToc = new BindingSource();
            bindingSourceDanToc.DataSource = dataTableDanToc;
            //Ton giao Data Source
            string QueryTonGiao = "SELECT TonGiaoID,TenTonGiao FROM DM_TonGiao";
            SqlDataAdapter sqlDataAdapterTonGiao = new SqlDataAdapter(QueryTonGiao, con);
            DataTable dataTableTonGiao = new DataTable();
            sqlDataAdapterTonGiao.Fill(dataTableTonGiao);
            BindingSource bindingSourceTonGiao = new BindingSource();
            bindingSourceTonGiao.DataSource = dataTableTonGiao;
            //Tinh Data Source
            string QueryTinh = "SELECT TinhID,TenTinh FROM DM_Tinh";
            SqlDataAdapter sqlDataAdapterTinh = new SqlDataAdapter(QueryTinh, con);
            DataTable dataTableTinh = new DataTable();
            sqlDataAdapterTinh.Fill(dataTableTinh);
            BindingSource bindingSourceTinh = new BindingSource();
            bindingSourceTinh.DataSource = dataTableTinh;
            //Dien uu tien Data Source
            string QueryDienUuTien = "SELECT DienUuTienID,TenDienUuTien FROM DM_DienUuTien";
            SqlDataAdapter sqlDataAdapterDienUuTien = new SqlDataAdapter(QueryDienUuTien, con);
            DataTable dataTableDienUuTien = new DataTable();
            sqlDataAdapterDienUuTien.Fill(dataTableDienUuTien);
            BindingSource bindingSourceDienUuTien = new BindingSource();
            bindingSourceDienUuTien.DataSource = dataTableDienUuTien;
            //Dien Uu dai Data Source
            string QueryDienUuDai = "SELECT DienUuDaiID,TenDienUuDai FROM DM_DienUuDai";
            SqlDataAdapter sqlDataAdapterDienUuDai = new SqlDataAdapter(QueryDienUuDai, con);
            DataTable dataTableDienUuDai = new DataTable();
            sqlDataAdapterDienUuDai.Fill(dataTableDienUuDai);
            BindingSource bindingSourceDienUuDai = new BindingSource();
            bindingSourceDienUuDai.DataSource = dataTableDienUuDai;
            //Tinh Data Source
            string QueryKhuyetTat = "SELECT KhuyetTatID,TenKhuyetTat FROM DM_KhuyetTat";
            SqlDataAdapter sqlDataAdapterKhuyetTat = new SqlDataAdapter(QueryKhuyetTat, con);
            DataTable dataTableKhuyetTat = new DataTable();
            sqlDataAdapterKhuyetTat.Fill(dataTableKhuyetTat);
            BindingSource bindingSourceKhuyetTat = new BindingSource();
            bindingSourceKhuyetTat.DataSource = dataTableKhuyetTat;
            //Nang khieu Data Source
            string QueryNangKhieu = "SELECT NangKhieuID,TenNangKhieu FROM DM_NangKhieu";
            SqlDataAdapter sqlDataAdapterNangKhieu = new SqlDataAdapter(QueryNangKhieu, con);
            DataTable dataTableNangKhieu = new DataTable();
            sqlDataAdapterNangKhieu.Fill(dataTableNangKhieu);
            BindingSource bindingSourceNangKhieu = new BindingSource();
            bindingSourceNangKhieu.DataSource = dataTableNangKhieu;
            //---------------------------------------------------------------------------

            //Add QUoc gia
            DataGridViewComboBoxColumn ColumnQuocGia = new DataGridViewComboBoxColumn();
            ColumnQuocGia.DataPropertyName = "FK_QuocTichID";
            ColumnQuocGia.HeaderText = "Quốc gia";
            ColumnQuocGia.Width = 120;
            ColumnQuocGia.DataSource = bindingSourceQuocGia;
            ColumnQuocGia.ValueMember = "QuocGiaID";
            ColumnQuocGia.DisplayMember = "TenQuocGia";
            dtHocSinh.Columns.Add(ColumnQuocGia);
            //Add dan toc
            DataGridViewComboBoxColumn ColumnDanToc = new DataGridViewComboBoxColumn();
            ColumnDanToc.DataPropertyName = "FK_DanTocID";
            ColumnDanToc.HeaderText = "Dân tộc";
            ColumnDanToc.Width = 120;
            ColumnDanToc.DataSource = bindingSourceDanToc;
            ColumnDanToc.ValueMember = "DanTocID";
            ColumnDanToc.DisplayMember = "TenDanToc";
            dtHocSinh.Columns.Add(ColumnDanToc);
            //Add Ton giao
            DataGridViewComboBoxColumn ColumnTonGiao = new DataGridViewComboBoxColumn();
            ColumnTonGiao.DataPropertyName = "FK_TonGiaoID";
            ColumnTonGiao.HeaderText = "Tôn giáo";
            ColumnTonGiao.Width = 120;
            ColumnTonGiao.DataSource = bindingSourceTonGiao;
            ColumnTonGiao.ValueMember = "TonGiaoID";
            ColumnTonGiao.DisplayMember = "TenTonGiao";
            dtHocSinh.Columns.Add(ColumnTonGiao);
            //Add Tinh
            DataGridViewComboBoxColumn ColumnTinh = new DataGridViewComboBoxColumn();
            ColumnTinh.DataPropertyName = "FK_MaTinhID";
            ColumnTinh.HeaderText = "Tỉnh";
            ColumnTinh.Width = 120;
            ColumnTinh.DataSource = bindingSourceTinh;
            ColumnTinh.ValueMember = "TinhID";
            ColumnTinh.DisplayMember = "TenTinh";
            dtHocSinh.Columns.Add(ColumnTinh);
            //Add Uu tien
            DataGridViewComboBoxColumn ColumnDienUuTien = new DataGridViewComboBoxColumn();
            ColumnDienUuTien.DataPropertyName = "FK_UuTienID";
            ColumnDienUuTien.HeaderText = "Ưu tiên";
            ColumnDienUuTien.Width = 120;
            ColumnDienUuTien.DataSource = bindingSourceDienUuTien;
            ColumnDienUuTien.ValueMember = "DienUuTienID";
            ColumnDienUuTien.DisplayMember = "TenDienUuTien";
            dtHocSinh.Columns.Add(ColumnDienUuTien);
            //Add Uu dai
            DataGridViewComboBoxColumn ColumnDienUuDai = new DataGridViewComboBoxColumn();
            ColumnDienUuDai.DataPropertyName = "FK_UuDaiID";
            ColumnDienUuDai.HeaderText = "Ưu đãi";
            ColumnDienUuDai.Width = 120;
            ColumnDienUuDai.DataSource = bindingSourceDienUuDai;
            ColumnDienUuDai.ValueMember = "DienUuDaiID";
            ColumnDienUuDai.DisplayMember = "TenDienUuDai";
            dtHocSinh.Columns.Add(ColumnDienUuDai);
            //Add Tinh
            DataGridViewComboBoxColumn ColumnKhuyetTat = new DataGridViewComboBoxColumn();
            ColumnKhuyetTat.DataPropertyName = "FK_KhuyetTatID";
            ColumnKhuyetTat.HeaderText = "Khuyết tật";
            ColumnKhuyetTat.Width = 120;
            ColumnKhuyetTat.DataSource = bindingSourceKhuyetTat;
            ColumnKhuyetTat.ValueMember = "KhuyetTatID";
            ColumnKhuyetTat.DisplayMember = "TenKhuyetTat";
            dtHocSinh.Columns.Add(ColumnKhuyetTat);
            //Add Tinh
            DataGridViewComboBoxColumn ColumnNangKhieu = new DataGridViewComboBoxColumn();
            ColumnNangKhieu.DataPropertyName = "FK_NangKhieuID";
            ColumnNangKhieu.HeaderText = "Năng khiếu";
            ColumnNangKhieu.Width = 120;
            ColumnNangKhieu.DataSource = bindingSourceNangKhieu;
            ColumnNangKhieu.ValueMember = "NangKhieuID";
            ColumnNangKhieu.DisplayMember = "TenNangKhieu";
            dtHocSinh.Columns.Add(ColumnNangKhieu);
            //--------------------------------------------------------------------
            //Setting Data Source for DataGridView

            //
            dtHocSinh.AutoGenerateColumns = true;
            dtHocSinh.DataSource = bs;
            dtHocSinh.AutoGenerateColumns = false;

            dtHocSinh.Columns["HocSinhID"].DisplayIndex = 0;
            dtHocSinh.Columns["HoDem"].DisplayIndex = 1;
            dtHocSinh.Columns["TenDem"].DisplayIndex = 2;
            dtHocSinh.Columns["Ten"].DisplayIndex = 3;
            dtHocSinh.Columns["NgaySinh"].DisplayIndex = 4;
            dtHocSinh.Columns["NoiSinh"].DisplayIndex = 5;
            dtHocSinh.Columns["GioiTinh"].DisplayIndex = 6;
            dtHocSinh.Columns["QueQuan"].DisplayIndex = 7;
            dtHocSinh.Columns["DanTocCheck"].DisplayIndex = 9;
            dtHocSinh.Columns["DiaChi"].DisplayIndex = 11;
            dtHocSinh.Columns["Email"].DisplayIndex = 12;
            dtHocSinh.Columns["DienThoai"].DisplayIndex = 13;
            dtHocSinh.Columns["GhiChu"].DisplayIndex = 20;
            dtHocSinh.Columns["DaPhanLop"].Visible = false;

            #endregion
        }

        private void NhapHSFile_Click(object sender, EventArgs e)
        {
            #region Hiện các textbox, label liên quan đến nhập từ file
            btnLuuNhapTay.Visible = false;
            lblDuongDan.Visible = true;
            tbxDuongDan.Visible = true;
            btnChonFile.Visible = true;
            btnLoadDuLieu.Visible = true;
            btnLuuDuLieu.Visible = true;
            dtHocSinh.Visible = false;
            dtHocSinhNhapTuFile.Visible = true;
            #endregion
        }

        private void btnChonFile_Click(object sender, EventArgs e)
        {
            #region Chọn file từ hộp thoại
            String strFileName = "";
            //First, declare a variable to hold the user’s file selection.
            String input = string.Empty;

            //Create a new instance of the OpenFileDialog because it's an object.
            OpenFileDialog dialog = new OpenFileDialog();

            //Now set the file type
            dialog.Filter = "excel files (*.xls)|*.xls;*.xlsx|All files (*.*)|*.*";

            //Set the starting directory and the title.
            dialog.InitialDirectory = "C:"; dialog.Title = "Select a excel file";

            //Present to the user.
            if (dialog.ShowDialog() == DialogResult.OK)
            {
                strFileName = dialog.FileName;
                tbxDuongDan.Text = strFileName;
            }
            if (strFileName == String.Empty)
                return;//user didn't select a file   
            #endregion
        }

        private void btnLoadDuLieu_Click(object sender, EventArgs e)
        {
            #region Load dữ liệu từ file excel được chọn
            String filename = tbxDuongDan.Text;
            if (filename.Equals("")) { MessageBox.Show("Chưa chọn file!"); return; }

            String connString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
            "Data Source=" + filename + ";Extended Properties=Excel 12.0;";

            Console.WriteLine(connString);
            OleDbConnection connection = new OleDbConnection(connString);
            string excelQuery = @"Select * FROM [Sheet1$]";
            connection.Open();
            OleDbCommand cmd = new OleDbCommand(excelQuery, connection);
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = cmd;
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            DataTable dt = ds.Tables[0];
            dtHocSinhNhapTuFile.DataSource = dt;

            //Console.WriteLine(dtHocSinhNhapTuFile.Rows[0].Cells[0].Value.ToString());
            //for (int i = 0; i < dtHocSinhNhapTuFile.Rows.Count - 1; i++)
            //{
            //    try
            //    {
            //        String tam = dtHocSinhNhapTuFile.Rows[i].Cells[0].Value.ToString();
            //        if (tam != null && !tam.Equals(""))
            //        {
            //            Console.Write("{0}", tam);
            //            Console.WriteLine();
            //        }
            //    }
            //    catch (Exception ex) { Console.WriteLine(ex); }
            //}
            #endregion

        }
        private void btnLuuDuLieu_Click(object sender, EventArgs e)
        {
            #region Lưu dữ liệu từ datagrid dtHocSinhNhapTuFile vào DB
            for (int i = 0; i < dtHocSinhNhapTuFile.Rows.Count - 1; i++)
            {
                var check = from ihs in db.HS_HoSoHocSinhs
                            where ihs.HocSinhID.Equals(dtHocSinhNhapTuFile.Rows[i].Cells[0].Value.ToString())
                            select ihs;
                if (check.Count() > 0) { continue; }

                HS_HoSoHocSinh hs = new HS_HoSoHocSinh();
                hs.HocSinhID = dtHocSinhNhapTuFile.Rows[i].Cells[0].Value.ToString();
                hs.HoDem = dtHocSinhNhapTuFile.Rows[i].Cells[1].Value.ToString();
                hs.TenDem = dtHocSinhNhapTuFile.Rows[i].Cells[2].Value.ToString();
                hs.Ten = dtHocSinhNhapTuFile.Rows[i].Cells[3].Value.ToString();
                hs.NgaySinh = DateTime.Parse(dtHocSinhNhapTuFile.Rows[i].Cells[4].Value.ToString());
                hs.NoiSinh = dtHocSinhNhapTuFile.Rows[i].Cells[5].Value.ToString();
                hs.GioiTinh = dtHocSinhNhapTuFile.Rows[i].Cells[6].Value.ToString();
                hs.QueQuan = dtHocSinhNhapTuFile.Rows[i].Cells[7].Value.ToString();
                hs.FK_QuocTichID = dtHocSinhNhapTuFile.Rows[i].Cells[8].Value.ToString();
                hs.FK_DanTocID = dtHocSinhNhapTuFile.Rows[i].Cells[9].Value.ToString();
                hs.FK_TonGiaoID = dtHocSinhNhapTuFile.Rows[i].Cells[10].Value.ToString();
                hs.DiaChi = dtHocSinhNhapTuFile.Rows[i].Cells[11].Value.ToString();
                hs.FK_MaTinhID = dtHocSinhNhapTuFile.Rows[i].Cells[12].Value.ToString();
                hs.Email = dtHocSinhNhapTuFile.Rows[i].Cells[13].Value.ToString();
                hs.DienThoai = dtHocSinhNhapTuFile.Rows[i].Cells[14].Value.ToString();
                hs.FK_UuTienID = dtHocSinhNhapTuFile.Rows[i].Cells[15].Value.ToString();
                hs.FK_UuDaiID = dtHocSinhNhapTuFile.Rows[i].Cells[16].Value.ToString();
                hs.FK_KhuyetTatID = dtHocSinhNhapTuFile.Rows[i].Cells[17].Value.ToString();
                hs.FK_NangKhieuID = dtHocSinhNhapTuFile.Rows[i].Cells[18].Value.ToString();
                hs.GhiChu = dtHocSinhNhapTuFile.Rows[i].Cells[19].Value.ToString();
                try
                {
                    db.HS_HoSoHocSinhs.InsertOnSubmit(hs);
                    db.SubmitChanges();
                }
                catch (Exception ex) { Console.WriteLine(ex); }
            }

            #endregion
        }

        private void btnLuuNhapTay_Click(object sender, EventArgs e)
        {
            #region Cập nhật DB khi thay đổi dữ liệu trên dtHocSinh
            SqlCommandBuilder cm = new SqlCommandBuilder(ap);
            ap.Update(tb);
            #endregion
        }
        //
    }
}
